
--Set the database to which these functions will be installed
USE Lib
GO


--Enable CLR Integration if is not enabled
/*
exec sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
*/


--Drop the functions if they already exist
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_list_agg' AND ROUTINE_TYPE = 'FUNCTION')
	DROP AGGREGATE [clr].[fn_list_agg]
GO

--Drop the functions if they already exist
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_median' AND ROUTINE_TYPE = 'FUNCTION')
	DROP AGGREGATE [clr].[fn_median]
GO


--Drop the assembly if it already exists
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'ClrAgg')
	DROP ASSEMBLY [ClrAgg]
GO

--Create the assembly
CREATE ASSEMBLY [ClrAgg] 
FROM 'C:\Program Files\Microsoft SQL Server\100\CLR\ClrAgg.dll' 
WITH PERMISSION_SET = SAFE
GO

----Create the functions
CREATE AGGREGATE [clr].[fn_list_agg] (@value NVARCHAR(MAX), @delimiter NVARCHAR(255), @qualifier NVARCHAR(255))
RETURNS NVARCHAR(MAX)	
EXTERNAL NAME ClrAgg.ListAgg


CREATE AGGREGATE clr.fn_median
(@input float)
RETURNS float
EXTERNAL NAME ClrAgg.Median

